const Router = require("@koa/router");
const { Pool } = require("pg");
const path = require('path');
require('dotenv').config({ path: path.resolve(__dirname, '../.env') });

const router = new Router({ prefix: "/products" });

// 配置数据库连接池
const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});

// 1. 查询所有商品 (GET /products)
router.get("/", async (ctx) => {
  try {
    const { page = 1, limit = 10, keyword } = ctx.query;
    const offset = (page - 1) * limit;
    
    let countQuery = "SELECT COUNT(*) FROM products";
    let dataQuery = "SELECT * FROM products";
    let queryParams = [];
    let whereClause = "";
    
    // 如果有关键词搜索，添加WHERE条件
    if (keyword && keyword.trim()) {
      const searchKeyword = `%${keyword.trim()}%`;
      whereClause = ` WHERE (
        COALESCE(product_id::text, '') ILIKE $1 OR 
        title_cn ILIKE $1 OR 
        title_en ILIKE $1 OR 
        seo_title_cn ILIKE $1 OR 
        seo_title_en ILIKE $1 OR 
        cate ILIKE $1 OR 
        COALESCE(shopify_id::text, '') ILIKE $1
      )`;
      queryParams.push(searchKeyword);
      countQuery += whereClause;
      dataQuery += whereClause;
    }
    
    // 获取总数
    const countResult = await pool.query(countQuery, queryParams);
    const total = parseInt(countResult.rows[0].count);
    
    // 获取分页数据
    dataQuery += ` ORDER BY created_at DESC LIMIT $${queryParams.length + 1} OFFSET $${queryParams.length + 2}`;
    queryParams.push(limit, offset);
    
    const result = await pool.query(dataQuery, queryParams);
    
    ctx.body = {
      success: true,
      data: result.rows,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total,
        totalPages: Math.ceil(total / limit)
      },
      keyword: keyword || null
    };
  } catch (err) {
    console.error(`${new Date().toLocaleString()} 获取商品列表失败:`, err);
    ctx.status = 500;
    ctx.body = { success: false, message: err.message };
  }
});

// 2. 获取所有商品的product_id (GET /products/ids)
router.get("/ids", async (ctx) => {
  try {
    const result = await pool.query("SELECT product_id FROM products ORDER BY created_at DESC");
    
    // 提取所有product_id到数组中
    const productIds = result.rows.map(row => row.product_id);
    
    ctx.body = {
      success: true,
      data: productIds,
      total: productIds.length
    };
  } catch (err) {
    console.error(`${new Date().toLocaleString()} 获取商品ID列表失败:`, err);
    ctx.status = 500;
    ctx.body = { success: false, message: err.message };
  }
});

// 3. 根据ID查询单个商品 (GET /products/:id)
router.get("/:id", async (ctx) => {
  try {
    const { id } = ctx.params;
    const result = await pool.query("SELECT * FROM products WHERE product_id = $1", [id]);
    
    if (result.rows.length === 0) {
      ctx.status = 404;
      ctx.body = { success: false, message: "商品不存在" };
      return;
    }
    
    ctx.body = { success: true, data: result.rows[0] };
  } catch (err) {
    console.error(`${new Date().toLocaleString()} 获取商品详情失败:`, err);
    ctx.status = 500;
    ctx.body = { success: false, message: err.message };
  }
});

// 3. 新增商品 (POST /products)
router.post("/", async (ctx) => {
  try {
    const {
      product_id,
      title_cn,
      title_en,
      seo_title_cn,
      seo_title_en,
      detail_images_cn,
      is_ok,
      is_ugly,
      main_images_cn,
      video_url,
      sku_data,
      product_html,
      shopify_id,
      param_info_en,
      param_info_cn,
      cate
    } = ctx.request.body;
    
    // 验证必填字段
    if (!title_cn || !product_id) {
      ctx.status = 400;
      ctx.body = { success: false, message: "中文标题和product_id为必填项" };
      return;
    }

    // 检查商品ID是否已存在
    const existingProduct = await pool.query(
      "SELECT product_id FROM products WHERE product_id = $1",
      [product_id]
    );
    if (existingProduct.rows.length > 0) {
      ctx.status = 400;
      ctx.body = { success: false, message: "商品ID已存在" };
      return
    }
    
    const result = await pool.query(
      `INSERT INTO products (
        product_id, title_cn, title_en, seo_title_cn, seo_title_en, detail_images_cn, is_ok, is_ugly,
        main_images_cn, video_url, sku_data, product_html, shopify_id, param_info_en, param_info_cn, cate
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16) RETURNING *`,
      [
        product_id,
        title_cn,
        title_en,
        seo_title_cn || "",
        seo_title_en || "",
        detail_images_cn || [],
        is_ok || 0,
        is_ugly || false,
        main_images_cn || [],
        video_url || '',
        JSON.stringify(sku_data || {}),
        product_html || "",
        shopify_id || null,
        param_info_en || "",
        param_info_cn || "",
        cate || ""
      ]
    );
    
    console.log(`${new Date().toLocaleString()} 新商品已创建:`, result.rows[0]);
    ctx.body = { success: true, data: result.rows[0], message: "商品创建成功" };
  } catch (err) {
    console.error(`${new Date().toLocaleString()} 创建商品失败:`, err);
    ctx.status = 500;
    ctx.body = { success: false, message: err.message };
  }
});

// 4. 更新商品 (PUT /products/:id)
router.put("/:id", async (ctx) => {
  try {
    const { id } = ctx.params;
    const {
      title_cn,
      title_en,
      seo_title_cn,
      seo_title_en,
      detail_images_cn,
      is_ok,
      is_ugly,
      main_images_cn,
      video_url,
      sku_data,
      product_html,
      shopify_id,
      param_info_en,
      param_info_cn,
      cate
    } = ctx.request.body;
    
    // 检查商品是否存在
    const existingProduct = await pool.query(
      "SELECT * FROM products WHERE product_id = $1",
      [id]
    );
    
    if (existingProduct.rows.length === 0) {
      ctx.status = 404;
      ctx.body = { success: false, message: "商品不存在" };
      return;
    }
    
    // 构建更新字段
    const updateFields = [];
    const updateValues = [];
    let paramIndex = 1;
    
    if (title_cn !== undefined) {
      updateFields.push(`title_cn = $${paramIndex++}`);
      updateValues.push(title_cn);
    }
    if (title_en !== undefined) {
      updateFields.push(`title_en = $${paramIndex++}`);
      updateValues.push(title_en);
    }
    if (seo_title_cn !== undefined) {
      updateFields.push(`seo_title_cn = $${paramIndex++}`);
      updateValues.push(seo_title_cn);
    }
    if (seo_title_en !== undefined) {
      updateFields.push(`seo_title_en = $${paramIndex++}`);
      updateValues.push(seo_title_en);
    }
    if (detail_images_cn !== undefined) {
      updateFields.push(`detail_images_cn = $${paramIndex++}`);
      updateValues.push(detail_images_cn);
    }
    if (is_ok !== undefined) {
      updateFields.push(`is_ok = $${paramIndex++}`);
      // 确保is_ok是整数类型，如果转换失败则使用0作为默认值
      const parsedIsOk = parseInt(is_ok, 10);
      updateValues.push(isNaN(parsedIsOk) ? 0 : parsedIsOk);
    }
    if (is_ugly !== undefined) {
      updateFields.push(`is_ugly = $${paramIndex++}`);
      updateValues.push(Boolean(is_ugly));
    }
    if (main_images_cn !== undefined) {
      updateFields.push(`main_images_cn = $${paramIndex++}`);
      updateValues.push(main_images_cn);
    }
    if (video_url !== undefined) {
      updateFields.push(`video_url = $${paramIndex++}`);
      updateValues.push(video_url);
    }
    if (sku_data !== undefined) {
      updateFields.push(`sku_data = $${paramIndex++}`);
      updateValues.push(JSON.stringify(sku_data));
    }
    if (product_html !== undefined) {
      updateFields.push(`product_html = $${paramIndex++}`);
      updateValues.push(product_html);
    }
    if (shopify_id !== undefined) {
      updateFields.push(`shopify_id = $${paramIndex++}`);
      updateValues.push(shopify_id);
    }
    if (param_info_en !== undefined) {
      updateFields.push(`param_info_en = $${paramIndex++}`);
      updateValues.push(param_info_en);
    }
    if (param_info_cn !== undefined) {
      updateFields.push(`param_info_cn = $${paramIndex++}`);
      updateValues.push(param_info_cn);
    }
    if (cate !== undefined) {
      updateFields.push(`cate = $${paramIndex++}`);
      updateValues.push(cate);
    }
    
    if (updateFields.length === 0) {
      ctx.status = 400;
      ctx.body = { success: false, message: "没有提供要更新的字段" };
      return;
    }
    
    updateValues.push(id);
    
    const query = `UPDATE products SET ${updateFields.join(", ")} WHERE product_id = $${paramIndex} RETURNING *`;
    const result = await pool.query(query, updateValues);
    
    console.log(`${new Date().toLocaleString()} 商品已更新:`, result.rows[0]);
    ctx.body = { success: true, data: result.rows[0], message: "商品更新成功" };
  } catch (err) {
    console.error(`${new Date().toLocaleString()} 更新商品失败:`, err);
    ctx.status = 500;
    ctx.body = { success: false, message: err.message };
  }
});

// 5. 删除商品 (DELETE /products/:id)
router.delete("/:id", async (ctx) => {
  try {
    const { id } = ctx.params;
    
    // 检查商品是否存在
    const existingProduct = await pool.query(
      "SELECT * FROM products WHERE product_id = $1",
      [id]
    );
    
    if (existingProduct.rows.length === 0) {
      ctx.status = 404;
      ctx.body = { success: false, message: "商品不存在" };
      return;
    }
    
    const result = await pool.query(
      "DELETE FROM products WHERE product_id = $1 RETURNING *",
      [id]
    );
    
    console.log(`${new Date().toLocaleString()} 商品已删除:`, result.rows[0]);
    ctx.body = { success: true, data: result.rows[0], message: "商品删除成功" };
  } catch (err) {
    console.error(`${new Date().toLocaleString()} 删除商品失败:`, err);
    ctx.status = 500;
    ctx.body = { success: false, message: err.message };
  }
});

module.exports = router;